IF EXISTS (
       SELECT *
       FROM   sys.objects
       WHERE  OBJECT_ID = OBJECT_ID(N'[dbo].[cms_sp_MediaContentSelectOne]')
              AND TYPE IN (N'P' ,N'PC')
   )
    DROP PROCEDURE [dbo].[cms_sp_MediaContentSelectOne]
GO
CREATE PROCEDURE [dbo].[cms_sp_MediaContentSelectOne] 
(@Id INT)
AS
BEGIN
	SELECT mc.MediaContentId    AS Id
	      ,mc.MediaId
	      ,mc.SiteId
	      ,mc.SiteGroupId
	      ,mc.MetaCode
	      ,mc.[Description]
	      ,mc.CreatedBy
	      ,mc.DateCreated
	      ,mc.LastUpdatedBy
	      ,mc.DateLastUpdated
	      ,mc.MediaContentName
	      ,mc.BlockFromSearchEngines
	      ,mf.MediaFileId       AS MainFile_Id
	      ,mf.MimeTypeId        AS MainFile_MimeTypeId
	      ,mf.OriginalFileName  AS MainFile_OriginalFileName
	      ,mf.LinkedFileName    AS MainFile_LinkedFileName
	      ,mf.FileSize          AS MainFile_FileSize
	      ,mf.SiteId            AS MainFile_SiteId
	      ,mf.SiteGroupId       AS MainFile_SiteGroupId
	      ,mf.Height            AS MainFile_Height
	      ,mf.Width             AS MainFile_Width
	      ,mf.CreatedBy         AS MainFile_CreatedBy
	      ,mf.DateCreated       AS MainFile_DateCreated
	      ,mf.LastUpdatedBy     AS MainFile_LastUpdatedBy
	      ,mf.DateLastUpdated   AS MainFile_DateLastUpdated
	      ,mt.MimeTypeName      AS MainFile_MimeType_MimeTypeName
	      ,mt.ContentType       AS MainFile_MimeType_ContentType
	      ,mt.IsImage           AS MainFile_MimeType_IsImage
	      ,mcfl.IsPrimary       AS MainFile_IsPrimary
	FROM   MediaContents mc
	       LEFT JOIN MediaContentFileLinks mcfl
	            ON  mcfl.MediaContentId = mc.MediaContentId
	                AND mcfl.IsPrimary = 1
	       LEFT JOIN MediaFiles mf
	            ON  mf.MediaFileId = mcfl.MediaFileId
	       LEFT JOIN MimeTypes mt
	            ON  mt.MimeTypeId = mf.MimeTypeId
	WHERE  mc.MediaContentId = @Id
END
GO